package com.mes.spc.util;

import com.yy.base.exception.BaseException;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.xssf.eventusermodel.XSSFReader;
import org.apache.poi.xssf.model.SharedStringsTable;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.xml.sax.Attributes;
import org.xml.sax.InputSource;
import org.xml.sax.SAXException;
import org.xml.sax.XMLReader;
import org.xml.sax.helpers.DefaultHandler;

import javax.xml.parsers.SAXParserFactory;
import java.io.File;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;

public class SPCParseExcel2007 extends DefaultHandler {
	private SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");//处理时间格式
	private boolean parseAll = false;//是否解析所有sheet
	private int startRowIndex = 0;
	private boolean startParse = false;
	
	private SharedStringsTable sst;
	private StringBuffer lastContents = new StringBuffer();//单元格数据
	private boolean nextIsString;//当前列是否是字符数据
	
	private int rowIndex;//行下标
	private String colIndex;//列对应的下标如：A1，B1
	private String colType;//2: 字符，3：时间
	private List<String[]> result = new ArrayList<String[]>();//数据结果
	private Map<String, String> rowData = new HashMap<String, String>();//没行数据
	private int maxCol = 0;
	
	/**
	 * 解析Excel2007
	 * file: 文件
	 */
	public List<String[]> parseExcel(File file) {
		return parseExcel(file, 0);
	}
	/**
	 * 解析Excel2007
	 * file: 文件
	 */
	public List<String[]> parseExcel(String filePath) {
		return parseExcel(new File(filePath), 0);
	}
	/**
	 * 是否解析所有sheet
	 */
	public void setParseAllSheet(boolean bool) {
		this.parseAll = bool;
	}

	/**
	 * 解析Excel2007
	 * file: 文件
	 * startRow： 开始行
	 */
	public List<String[]> parseExcel(File file, int startRow) {
		try {
			process(file);
			return result;
		} catch (Exception e) {
			throw new BaseException("文件读取错误。详细错误!", e);
		}
	}

	/**
	 * 读取所有工作簿的入口方法
	 * 通过解析xml方式读取excel2007
	 */
	private void process(File file) throws Exception {
		OPCPackage pkg = OPCPackage.open(file);
		XSSFReader r = new XSSFReader(pkg);
		SharedStringsTable sst = r.getSharedStringsTable();
		XMLReader parser = fetchSheetParser(sst);
		Iterator<InputStream> sheets = r.getSheetsData();
		while (sheets.hasNext()) {
			InputStream sheet = sheets.next();
			InputSource sheetSource = new InputSource(sheet);
			parser.parse(sheetSource);
			sheet.close();
			if (!parseAll) {
				return;
			}
		}
	}

	private XMLReader fetchSheetParser(SharedStringsTable sst) throws SAXException {
//		XMLReader parser = XMLReaderFactory.createXMLReader("org.apache.xerces.parsers.SAXParser");
		SAXParserFactory m_parserFactory = null;
// If unable to create an instance, let's try to use
// the XMLReader from JAXP
		m_parserFactory = SAXParserFactory.newInstance();
		m_parserFactory.setNamespaceAware(true);
		XMLReader parser = null;
		try {
			parser = m_parserFactory.newSAXParser().getXMLReader();
		}catch (Exception e){
			e.printStackTrace();
		}
		this.sst = sst;
		parser.setContentHandler(this);
		return parser;
	}

	public void startElement(String uri, String localName, String name, Attributes attributes) throws SAXException {
		// c => 单元格
		if ("c".equals(name)) {
			// 如果下一个元素是 SST 的索引， 则将nextIsString标记为true
			String cellType = attributes.getValue("t");
			colIndex = attributes.getValue("r");
			colType = attributes.getValue("s");
			
			if ("s".equals(cellType)) {
				nextIsString = true;
			} else {
				nextIsString = false;
			}
		} else if ("row".equals(name)) {
			//获取当前行总列数
			String spans = attributes.getValue("spans");
			maxCol = Integer.parseInt(spans.split(":")[1]);
			//行下标
			rowIndex = Integer.parseInt(attributes.getValue("r"));
			if (rowIndex >= startRowIndex) {
				startParse = true;
			}
			//清空行数据
			rowData.clear();
		}
		clearContent();
	}

	public void endElement(String uri, String localName, String name) throws SAXException {
		if (!startParse) {
			return;
		}
		// 根据SST的索引值的到单元格的真正要存储的字符串
		// 这时characters()方法可能会被调用多次
		// v => 单元格的值，如果单元格是字符串则v标签的值为该字符串在SST中的索引
		if (name.equals("v")) {
			if (nextIsString) {
				try {
					int idx = Integer.parseInt(lastContents.toString());
					clearContent();
					lastContents.append(new XSSFRichTextString(sst.getEntryAt(idx)).toString());
				} catch (Exception e) {
				}
			}
			
			String value = lastContents.toString().trim();
			try {
				Double dbValue = Double.valueOf(value);
				//记录每行数据
				if ("1".equals(colType)||"2".equals(colType)||"3".equals(colType)) {//时间类型
					rowData.put(colIndex, sdf.format(DateUtil.getJavaDate(dbValue)));
				} else {//数字类型
					if (dbValue > dbValue.longValue()) {
						rowData.put(colIndex, formatNumber(dbValue.doubleValue()));
					} else {
						rowData.put(colIndex, dbValue.longValue() + "");
					}
				}
			} catch (Exception e) {
				rowData.put(colIndex, value);
			}
		} else if (name.equals("row")) {// 如果标签名称为 row ，这说明已到行尾，调用 optRows() 方法
			setRowData();
		}
	}

	public void characters(char[] ch, int start, int length) throws SAXException {
		if (!startParse) {
			return;
		}
		// 得到单元格内容的值
		lastContents.append(ch, start, length);
	}

	// 置空
	private void clearContent() {
		lastContents.delete(0, lastContents.length());
	}
	
	/**
	 * 解析完一行数据后整理此行数据
	 */
	private void setRowData() {
		if (!startParse) {
			return;
		}
		String[]data = new String[maxCol];
		String key = null;
		for (int i = 0; i < maxCol; i++) {
			key = getExcelIndex(i + 1) + "" + rowIndex;
			data[i] = rowData.get(key);
		}
		result.add(data);
	}
	//处理科学计算法的数字数据
	private String formatNumber(Double value) {
		if ((value + "").indexOf("-") > -1) {
			int size = Integer.parseInt((value + "").split("-")[1]);
			StringBuilder str = new StringBuilder("#.");
			for (int i = 0;i < size;i ++) {
				str.append("#");
			}
			DecimalFormat df = new DecimalFormat(str.toString());
			return df.format(value);
		}
		return value + "";
	}
	
	//10 进制转26进制
	private String getExcelIndex(int i) {
		char[] allChar = "ABCDEFGHIJKLMNOPQRSTUVWXYZ".toCharArray();
		StringBuilder sb = new StringBuilder();
		while (i > 0) {
			sb.append(allChar[i % 26 - 1]);
			i /= 26;
		}
		return sb.reverse().toString();
	}
}